//------------------------------------------------------------------------
// Abstract: Implement of class SQLiteDBHelper
//
// Authoring: XiaoHanxiong
//
// Time: 2014.03.14
//
// Static test:
//
// Version history:
//		Ver_1.0	: 2014.03.14	XiaoHanxiong
//------------------------------------------------------------------------
#include "Stdafx.h"
#include "SQLiteDBHelper.h"
#include "SQLiteDB.h"

namespace Database
{
	// SQLite open flag
	#define SQLITE_OPEN_READONLY	0x1
	#define SQLITE_OPEN_READWRITE	0x2
	#define SQLITE_OPEN_CREATE		0x4


	SQLiteDBHelper::SQLiteDBHelper( SQLiteDB* database )
		: IDatabaseHelper(SQLite)
		, m_database(database)
		, m_nMax(0)
		, m_curIndex(0)
	{
		CheckIDatabase();
	}

	SQLiteDBHelper::~SQLiteDBHelper()
	{
		
	}

	void SQLiteDBHelper::CheckIDatabase()
	{
		if(DBAdministrator::GetDatabaseType(m_database) != SQLite)
			m_database = NULL;
	}

	bool SQLiteDBHelper::Create( const char* database )
	{
		if(m_database == NULL)
			return false;
		return m_database->Open(database, SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE);
	}

	bool SQLiteDBHelper::Connect( const char* database )
	{
		if(m_database == NULL)
			return false;
		return m_database->Open(database, SQLITE_OPEN_READWRITE);
	}

	bool SQLiteDBHelper::Open()
	{
		if(m_database == NULL)
			return false;
		return m_database->Open("", SQLITE_OPEN_READWRITE);
	}

	void SQLiteDBHelper::Close()
	{
		if(m_database != NULL)
		{
			m_database->Close();
			m_nMax = 0;
			m_curIndex = 0;
		}
	}

	bool SQLiteDBHelper::ExecuteSQL( const char* sql )
	{
		if(m_database == NULL)
			return false;
		return m_database->ExecuteSQL(sql);
	}

	bool SQLiteDBHelper::PrepareSQL( const char* sql )
	{
		if(m_database == NULL)
			return false;
		return m_database->PrepareSQL(sql);
	}

	const char* SQLiteDBHelper::GetLastError()
	{
		if(m_database == NULL)
			return false;
		return m_database->GetLastError();
	}

	bool SQLiteDBHelper::PrepareInsert( Table& table )
	{
		string sql;
		int field_count;

		if(m_database == NULL)
			return false;

		field_count = table.GetFieldCount();
		
		// Construct SQL
		sql += "INSERT INTO " + table.GetTableName() + " VALUES";
		sql += "(";
		for( int n = 0; n < field_count; n++ )
		{
			Field field;
			table.GetField(n, field);
			
			if(n != 0)
			{
				sql += ", :" + field.GetFieldName();
			}
			else
			{
				sql += ":" + field.GetFieldName();
			}
		}
		sql += ");";

		return m_database->PrepareSQL(sql.c_str());
	}

#if 0
	bool SQLiteDBHelper::Insert( Table& table )
	{
		if(m_database == NULL)
			return false;

		// If archive max number count, insert record to database
		if(--m_curIndex == 0 && m_nMax > 0)
		{
			// Commit data to database
			code = m_database->ExecuteSQL("COMMIT;");
			m_curIndex = m_nMax;
		}
		else
		{
			Table::RecordColloction& record_clt = table.GetRecordColloction();
			size_t record_count = record_clt.size();
			bool code = 0;

			for(size_t n = 0; n < record_count; n++)
			{
				Record& record = record_clt[n];
				size_t field_count = record.m_recordData.size();
				for(size_t column = 0; column < field_count; column++)
				{
					FieldData& field_data = record.m_recordData[column];
					string str;

					switch(field_data.GetDataType())
					{
					case EFT_BOOL:
					case EFT_INT8:
					case EFT_UINT8:
					case EFT_INT16:
					case EFT_UINT16:
					case EFT_INT32:
					case EFT_UINT32:
						m_database->Set(field_data.GetIndex(), *static_cast<unsigned int*>(field_data.GetDataPtr()));
						break;

					case EFT_INT64:
					case EFT_UINT64:
						m_database->Set(field_data.GetIndex(), *static_cast<unsigned __int64*>(field_data.GetDataPtr()));
						break;

					case EFT_FLOAT:
					case EFT_DOUBLE:
						m_database->Set(field_data.GetIndex(), *static_cast<double*>(field_data.GetDataPtr()));
						break;

					case EFT_CHAR:
					case EFT_TEXT:
						str = static_cast<char*>(field_data.GetDataPtr());
						m_database->Set(field_data.GetIndex(), str);
						break;

					case EFT_BLOB:
						m_database->Set(field_data.GetIndex(), field_data.GetDataPtr(), field_data.GetDataLength());
						break;

					case EFT_DATE:
						m_database->Set(field_data.GetIndex(), field_data.GetDataPtr(), field_data.GetDataLength());
						break;

					default:
						m_database->Set(field_data.GetIndex());
						break;
					}
				}
				
				// Insert data to database
				m_database->Insert();
			}

			// Clear table data cache and return true
			table.ClearRecordData();
			return code;
		}
		
		return false;
	}
#endif

	bool SQLiteDBHelper::Insert()
	{
		if(m_database == NULL)
			return false;

		bool code = false;

		if(m_nMax > 0)
		{
			if(m_curIndex > 0)
			{
				code = m_database->Insert();
				m_curIndex--;
			}

			if(m_curIndex == 0)
			{
				// If archive max number count, commit data to database
				m_database->ExecuteSQL("COMMIT;");
				m_curIndex = m_nMax;
			}
		}
		else
		{
			code = m_database->Insert();
			m_database->ExecuteSQL("COMMIT;");
		}

		return code;
	}

	void SQLiteDBHelper::SetWMax( int max )
	{
		if(m_database == NULL)
			return;

		if(max > 0)
		{
			m_nMax = max;
			m_curIndex = m_nMax;
		}
		else
		{
			m_curIndex = 0;
			m_nMax = 0;
		}
	}

	bool SQLiteDBHelper::PrepareQuery( Table& table )
	{
		string sql, field_sql;
		int field_count;

		if(m_database == NULL)
			return false;

		field_count = table.GetFieldCount();

		// Construct field list of SQL
		for( int n = 0; n < field_count; n++ )
		{
			Field field;
			table.GetField(n, field);
			
			if(n != 0)
			{
				field_sql += "," + field.GetFieldName();
			}
			else
			{
				field_sql += field.GetFieldName();
			}
		}

		// Construct query SQL
		sql = "SELECT " + field_sql + " FROM " + table.GetTableName() + ";";
		
		return m_database->PrepareSQL(sql.c_str());
	}

	bool SQLiteDBHelper::Next()
	{
		if(m_database == NULL)
			return false;
		return m_database->Step();
	}
	
	/*
		Abstract: Execute to insert some records into database file
		@Table table[in]	1.table name
							2.table field map
							3.records that need to insert 
	*/
	bool SQLiteDBHelper::ExecuteInsert( Table& table )
	{
		if(m_database == NULL)
			return false;

		// Perpare insert SQL
		if(PrepareInsert(table) == false)
			return false;

		// Insert field data into database
		Table::RecordColloction& record_clt = table.GetRecordColloction();
		size_t record_count = record_clt.size();
		Table::FieldMap& field_map = table.GetFieldMap();
		bool code = 0;

		for(size_t n = 0; n < record_count; n++)
		{
			Record& record = record_clt[n];

			Record::DataContainerMap::iterator itr = record.m_record.begin();
			Record::DataContainerMap::iterator end = record.m_record.end();
			for( ; itr != end; itr++)
			{
				int index = itr->first;
				DataContainer& data = itr->second;

				// Set column value
				switch(data.GetDataFlag())
				{
				case DataContainer::_Int64:
					m_database->Set(index, static_cast<unsigned __int64>(data.m_data.um_uint64));
					break;

				case DataContainer::_Double:
					m_database->Set(index, data.m_data.um_double);
					break;

				case DataContainer::_String:
					m_database->Set(index, static_cast<const char*>(data.m_data.um_blob.m_ptr), 
						data.m_data.um_blob.m_size);
					break;

				case DataContainer::_Blob:
					m_database->Set(index, static_cast<const void*>(data.m_data.um_blob.m_ptr), 
						data.m_data.um_blob.m_size);
					break;

				default:
					m_database->Set(index);
					break;
				}
			}

			// Insert data to database
		
			if(m_database->Insert() == false)
			{
				m_database->CloseSQL();
				return false;
			}
		}

		return true;
	}

	/*
		Abstract: Execute to query some records in database file
		@Table table[in]	1.table name
							2.table field map
		@Table table[out]	1.query result
	*/
	bool SQLiteDBHelper::ExecuteQuery( Table& table, int num_max )
	{
		if(m_database == NULL || num_max <= 0)
			return false;

		// Query the fields in the table(maybe not all)
		// Prepare query SQL for getting the field name and type
		if(PrepareQuery(table) == false)
			return false;

		// Get the table fields count
		int field_count = 0;
		if(m_database->GetColumnCount(field_count) == false || 
			m_database->Step() == false)
			return false;

		// Get the table fields data type
		std::map<int, FieldType> field_map;

		// Get column type
		for( int n = 0; n < field_count; n++ )
		{
			string column_name;
			SQLiteDataType column_type;

			m_database->GetColumnName(n, column_name);
			m_database->GetColumnType(n, column_type);

			switch(column_type)
			{
			case ESD_INTEGER:
				field_map[n] = EFT_UINT64;
				break;

			case ESD_FLOAT:
				field_map[n] = EFT_DOUBLE;
				break;

			case ESD_TEXT:
				field_map[n] = EFT_TEXT;
				break;

			case ESD_BLOB:
				field_map[n] = EFT_BLOB;
				break;

			case ESD_NULL:
				field_map[n] = EFT_NULL;
				break;
			}
		}

		// Clear database data colloction
		table.ClearRecord();
		// Close the database SQL statement
		m_database->CloseSQL();
		

		// Get table record count
		// If the record count is 0, then return true directly
		// Else resize the record collection container
		int record_count = 0;
		int count = 0;
		if(GetRecordCount(table.GetTableName(), record_count) == false)
			return false;
		if(record_count > 0)
		{
			count = record_count < num_max ? record_count : num_max;
			table.GetRecordColloction().resize(count);
		}
		else return true; 


		// Prepare query SQL for get column data
		if(PrepareQuery(table) == false)
			return false;
		
		// Get column data
		int num = 0;
		while(m_database->Step() && num < count)
		{
			// Append a new record
			Record& record = table.GetRecordColloction()[num++];
			for(int n = 0; n < field_count; n++)
			{
				DataContainer& data_container = record.GetDataContainer(n);
				switch(field_map[n])
				{
				case EFT_UINT64:
					{
						unsigned __int64 tmp = 0;
						m_database->Get(n, tmp);
						data_container.SetData(tmp);
						break;
					}

				case EFT_DOUBLE:
					{
						double tmp = 0;
						m_database->Get(n, tmp);
						data_container.SetData(tmp);
						break;
					}

				case EFT_TEXT:
					{
						string tmp;
						m_database->Get(n, tmp);
						data_container.SetData(tmp);
						break;
					}

				case EFT_BLOB:
					{
						const void* ptr = NULL;
						int size = 0;
						m_database->Get(n, ptr, size);
						data_container.SetData(ptr, size);
						break;
					}

				default:
					break;
				}// switch, one column
			}// end for, one record
		}// while, all data

		m_database->CloseSQL();
		return true;
	}

	bool SQLiteDBHelper::IsTblExist( string table_name )
	{
		string sql("SELECT NAME FROM SQLITE_MASTER WHERE TYPE = 'table' ORDER BY NAME;");
		string str;
		int seq = 0;

		if(m_database == NULL)
			return false;

		m_database->PrepareSQL(sql.c_str());
		while(m_database->Step())
		{
			m_database->Get(0, str);
			if(str == table_name)
			{
				m_database->CloseSQL();
				return true;
			}
		}

		return false;
	}

	bool SQLiteDBHelper::Drop( string table_name )
	{
		string sql;

		if(m_database == NULL)
			return false;

		sql = "DROP TABLE " + table_name + ";";
		return m_database->ExecuteSQL(sql.c_str());
	}

	bool SQLiteDBHelper::Truncate( string table_name )
	{
		string sql;

		if(m_database == NULL)
			return false;

		sql = "DELETE FROM " + table_name + ";";
		return m_database->ExecuteSQL(sql.c_str());
	}

	bool SQLiteDBHelper::GetRecordCount( string table_name, int& count )
	{
		string sql;
		sql = "SELECT COUNT(*) FROM " + table_name + ";";

		if(m_database == NULL)
			return false;

		if(m_database->PrepareSQL(sql.c_str()) == true)
		{
			unsigned int tmp = 0;
			m_database->Step();
			m_database->Get(0, tmp);

			count = static_cast<int>(tmp);
			m_database->CloseSQL();
			return true;
		}

		return false;
	}

	bool SQLiteDBHelper::GetTableFields( string table_name, Table& table )
	{
		if(m_database == NULL || table_name.empty())
			return false;

		// Query all the fields in the table
		// Construct SQL
		string sql;
		sql = "SELECT * FROM " + table_name + ";";
		if(m_database->PrepareSQL(sql.c_str()) == false)
			return false;
		
		// Get column count
		int column_count = 0;
		if(m_database->GetColumnCount(column_count) == false)
			return false;

		// Clear table field information
		table.GetFieldMap().clear();
		
		// Get column name
		for( int n = 0; n < column_count; n++ )
		{
			string column_name;

			m_database->GetColumnName(n, column_name);
			if(table.AddField(column_name, EFT_NULL) == false)
			{
				table.GetFieldMap().clear();
				return false;
			}
		}

		table.SetTableName(table_name);
		m_database->CloseSQL();
		return true;
	}

	bool SQLiteDBHelper::CreateTable( Table& table )
	{
		string sql, field_sql;
		int field_count = table.GetFieldCount();

		if(m_database == NULL)
			return false;

		for( int n = 0; n < field_count; n++ )
		{
			Field field;
			if(table.GetField(n, field) == true)
			{
				string sql_data_type;
				switch(field.GetFieldType())
				{
				case EFT_BOOL:
				case EFT_INT8:
				case EFT_UINT8:
				case EFT_INT16:
				case EFT_UINT16:
				case EFT_INT32:
				case EFT_UINT32:
				case EFT_INT64:
				case EFT_UINT64:
					sql_data_type += "INTEGER";
					break;

				case EFT_FLOAT:
				case EFT_DOUBLE:
					sql_data_type += "REAL";
					break;

				case EFT_CHAR:
				case EFT_TEXT:
					sql_data_type += "TEXT";
					break;

				case EFT_BLOB:
					sql_data_type += "BLOB";
					break;

				case EFT_DATE:
					sql_data_type += "DATE";
					break;

				default:
					break;
				}

				if( n == 0 )
				{
					field_sql += sql_data_type + " " + field.GetFieldName();
				}
				else
				{
					field_sql += ", " + sql_data_type + " " + field.GetFieldName();
				}
			}
		}

		sql = "CREATE TABLE " + table.GetTableName() + " (" +  field_sql + ");";
		return m_database->ExecuteSQL(sql.c_str());
	}

	bool SQLiteDBHelper::Rename( string& old_name, string& new_name )
	{
		if(m_database == NULL || old_name.empty() || new_name.empty())
			return false;

		string sql;
		sql = "ALTER TABLE " + old_name + " RENAME TO " + new_name + ";";
		return m_database->ExecuteSQL(sql.c_str());
	}

	bool SQLiteDBHelper::CreateIndex()
	{
		return false;
	}

	bool SQLiteDBHelper::Commit()
	{
		if(m_database == NULL)
			return false;
		return m_database->ExecuteSQL("COMMIT;");
	}

	bool SQLiteDBHelper::Rollback()
	{
		if(m_database == NULL)
			return false;
		return m_database->ExecuteSQL("ROLLBACK;");
	}

	bool SQLiteDBHelper::Transaction( bool flag )
	{
		if(m_database == NULL)
			return false;

		if(flag == true)
		{
			return m_database->ExecuteSQL("BEGIN;");
		}
		else
		{
			return m_database->ExecuteSQL("END;");
		}
	}

	bool SQLiteDBHelper::Synchronous( int mode )
	{
		if(m_database == NULL)
			return false;

		switch(mode)
		{
		case 0x0:
			return m_database->ExecuteSQL("PRAGMA synchronous = OFF;");

		case 0x1:
			return m_database->ExecuteSQL("PRAGMA synchronous = NORMAL;");

		case 0x2:
			return m_database->ExecuteSQL("PRAGMA synchronous = FULL;");

		default:
			return false;
		}

		return false;
	}

	/*
		Set field value by field name
	*/
	bool SQLiteDBHelper::Set( int index, bool value )
	{
		if(m_database != NULL)
			return m_database->Set(index, static_cast<unsigned int>(value));
		else return false;
	}

	bool SQLiteDBHelper::Set( int index, char value )
	{
		if(m_database != NULL)
			return m_database->Set(index, static_cast<unsigned int>(value));
		else return false;
	}

	bool SQLiteDBHelper::Set( int index, unsigned char value )
	{
		if(m_database != NULL)
			return m_database->Set(index, static_cast<unsigned int>(value));
		else return false;
	}

	bool SQLiteDBHelper::Set( int index, short value )
	{
		if(m_database != NULL)
			return m_database->Set(index, static_cast<unsigned int>(value));
		else return false;
	}

	bool SQLiteDBHelper::Set( int index, unsigned short value )
	{
		if(m_database != NULL)
			return m_database->Set(index, static_cast<unsigned int>(value));
		else return false;
	}

	bool SQLiteDBHelper::Set( int index, int value )
	{
		if(m_database != NULL)
			return m_database->Set(index, static_cast<unsigned int>(value));
		else return false;
	}

	bool SQLiteDBHelper::Set( int index, unsigned int value )
	{
		if(m_database != NULL)
			return m_database->Set(index, static_cast<unsigned int>(value));
		else return false;
	}

	bool SQLiteDBHelper::Set( int index, __int64 value )
	{
		if(m_database != NULL)
			return m_database->Set(index, static_cast<unsigned __int64>(value));
		else return false;
	}

	bool SQLiteDBHelper::Set( int index, unsigned __int64 value )
	{
		if(m_database != NULL)
			return m_database->Set(index, static_cast<unsigned int>(value));
		else return false;
	}

	bool SQLiteDBHelper::Set( int index, float value )
	{
		if(m_database != NULL)
			return m_database->Set(index, static_cast<double>(value));
		else return false;
	}

	bool SQLiteDBHelper::Set( int index, double value )
	{
		if(m_database != NULL)
			return m_database->Set(index, static_cast<double>(value));
		else return false;
	}

	bool SQLiteDBHelper::Set( int index, string& value )
	{
		if(m_database != NULL)
			return m_database->Set(index, value);
		else return false;
	}

	bool SQLiteDBHelper::Set( int index, const void* blob, int size )
	{
		if(m_database != NULL)
			return m_database->Set(index, blob, size);
		else return false;
	}

	bool SQLiteDBHelper::Set( int index )
	{
		if(m_database != NULL)
			return m_database->Set(index);
		else return false;
	}

	/* 
		Get field data by index
	*/
	bool SQLiteDBHelper::Get( int index, bool& value )
	{
		if(m_database != NULL)
		{
			unsigned int tmp = 0;
			if(m_database->Get(index, tmp) == true)
			{
				value = static_cast<bool>(tmp);
				return true;
			}
		}
		return false;
	}

	bool SQLiteDBHelper::Get( int index, char& value )
	{
		if(m_database != NULL)
		{
			unsigned int tmp = 0;
			if(m_database->Get(index, tmp) == true)
			{
				value = static_cast<char>(tmp);
				return true;
			}
		}
		return false;
	}

	bool SQLiteDBHelper::Get( int index, unsigned char& value )
	{
		if(m_database != NULL)
		{
			unsigned int tmp = 0;
			if(m_database->Get(index, tmp) == true)
			{
				value = static_cast<unsigned char>(tmp);
				return true;
			}
		}
		return false;
	}

	bool SQLiteDBHelper::Get( int index, short& value )
	{
		if(m_database != NULL)
		{
			unsigned int tmp = 0;
			if(m_database->Get(index, tmp) == true)
			{
				value = static_cast<short>(tmp);
				return true;
			}
		}
		return false;
	}

	bool SQLiteDBHelper::Get( int index, unsigned short& value )
	{
		if(m_database != NULL)
		{
			unsigned int tmp = 0;
			if(m_database->Get(index, tmp) == true)
			{
				value = static_cast<unsigned short>(tmp);
				return true;
			}
		}
		return false;
	}

	bool SQLiteDBHelper::Get( int index, int& value )
	{
		if(m_database != NULL)
		{
			unsigned int tmp = 0;
			if(m_database->Get(index, tmp) == true)
			{
				value = static_cast<int>(tmp);
				return true;
			}
		}
		return false;
	}

	bool SQLiteDBHelper::Get( int index, unsigned int& value )
	{
		if(m_database != NULL)
		{
			unsigned int tmp = 0;
			if(m_database->Get(index, tmp) == true)
			{
				value = static_cast<unsigned int>(tmp);
				return true;
			}
		}
		return false;
	}

	bool SQLiteDBHelper::Get( int index, __int64& value )
	{
		if(m_database != NULL)
		{
			unsigned __int64 tmp = 0;
			if(m_database->Get(index, tmp) == true)
			{
				value = static_cast<__int64>(tmp);
				return true;
			}
		}
		return false;
	}

	bool SQLiteDBHelper::Get( int index, unsigned __int64& value )
	{
		if(m_database != NULL)
		{
			unsigned __int64 tmp = 0;
			if(m_database->Get(index, tmp) == true)
			{
				value = static_cast<unsigned __int64>(tmp);
				return true;
			}
		}
		return false;
	}

	bool SQLiteDBHelper::Get( int index, float& value )
	{
		if(m_database != NULL)
		{
			double tmp = 0;
			if(m_database->Get(index, tmp) == true)
			{
				value = static_cast<float>(tmp);
				return true;
			}
		}
		return false;
	}

	bool SQLiteDBHelper::Get( int index, double& value )
	{
		if(m_database != NULL)
		{
			double tmp = 0;
			if(m_database->Get(index, tmp) == true)
			{
				value = static_cast<double>(tmp);
				return true;
			}
		}
		return false;
	}

	bool SQLiteDBHelper::Get( int index, string& value )
	{
		if(m_database != NULL)
		{
			return m_database->Get(index, value);
		}
		return false;
	}

	bool SQLiteDBHelper::Get( int index, const void*& blob, int& size )
	{
		if(m_database != NULL)
		{
			return m_database->Get(index, blob, size);
		}
		return false;
	}

}

